#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys

sys.path.append("/work/opt/analytic/my_python")
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from topinfo_conn import *
import re
import time

reload(sys)
sys.setdefaultencoding('utf-8')


def get_top_info_data(date):
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_info_stats"

    query = "select topinfoid,topid,userid,toptype,cateid,localid,tophour,starttime,endtime,addtime,state,amount,topsortid from topinfo_topinfo where date(addtime)='%s' order by topinfoid asc" % date
    #print query
    items = conn.get_results(query)
    for item in items:
        top_info_id = item[0]
        top_id = item[1]
        user_id = item[2]
        top_type = item[3]
        cate_id = item[4]
        city = item[5]
        top_hour = item[6]
        start_time = item[7]
        end_time = item[8]
        add_time = item[9]
        state = item[10]
        amount = item[11]
        top_sortid = item[12]

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]

        dc = get_cates(cate_id, stat_conn)
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name = ''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name = dc[1][1]

        query = 'select count(1) from portal_info where user_id=%s' % user_id
        items = stat_conn.get_results(query)
        user_type = items[0][0]

        param = [top_info_id, top_id, user_id, user_type, city_id, city_name, area_id, area_name, local_id, local_name,
                 cat1, cat1name, cat2, cat2name, 0, '', add_time, top_type, amount, top_hour, start_time, end_time,
                 state, top_sortid]
        stat_conn.insert_record(stat_table, param)
    conn.close()
    stat_conn.close()


def get_top_info_price_data(date):
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_info_specialprice"

    query = "select ifnull(max(config_id),0) from top_info_specialprice"
    items = stat_conn.get_results(query)
    max_id = items[0][0]

    #
    sep = '-'
    fmt = sep.join(['%Y', '%m', '%d'])
    start_date = time.strptime('2010-01-01', fmt)
    today = time.strptime(get_today(), fmt)
    today_seconds = (int)(time.mktime(today) - time.mktime(start_date))
    print today_seconds

    query = "select configid,localid,cateid,pricenum,effid,failid,addtime from topinfo_specialprice where configid>%s and failid>%s order by configid asc" % (
        max_id, today_seconds)
    #print query
    items = conn.get_results(query)
    for item in items:
        config_id = item[0]
        cate_id = item[2]
        city = item[1]
        price_num = item[3]
        eff_id = item[4]
        fail_id = item[5]
        add_time = item[6]

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]

        dc = get_cates(cate_id, stat_conn)
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name = ''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name = dc[1][1]

        param = (
            config_id, city_id, city_name, area_id, area_name, local_id, local_name, cat1, cat1name, cat2, cat2name, 0,
            '',
            price_num, add_time, eff_id, fail_id, eff_id, fail_id)
        insert_sql = " insert into %s(config_id,city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,price_num,add_time,eff_time,fail_time,eff_id,fail_id )" % stat_table
        insert_sql += " values(%s,%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',DATE_ADD('2010-01-01', INTERVAL %s/86400 DAY),DATE_ADD('2010-01-01', INTERVAL %s/86400 DAY),%s,%s) " % param

        stat_conn.execute_sql(insert_sql)
    conn.close()
    stat_conn.close()


def get_top_info_cate_stat(date):
    conn = set_stats_conn();
    stat_table = "top_info_cate_stats"
    query = "select city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,user_type,count(distinct user_id),sum(pay_amount/if(datediff(end_time,start_time)<1,1,datediff(end_time,start_time))),sum(top_hour/if(datediff(end_time,start_time)<1,1,datediff(end_time,start_time))) from top_info_stats where date(start_time)<='%s' and date(end_time)>='%s' group by city_id,area_id,local_id,cate1_id,cate2_id,cate3_id,user_type " % (
        date, date)
    items = conn.get_results(query)
    print len(items)
    for item in items:
        city_id = item[0]
        city_name = item[1]
        area_id = item[2]
        area_name = item[3]
        local_id = item[4]
        local_name = item[5]

        cat1 = item[6]
        cat1name = item[7]
        cat2 = item[8]
        cat2name = item[9]
        cat3 = item[10]
        cat3name = item[11]

        user_type = item[12]
        user_count = item[13]
        pay_amount = item[14]
        total_top_hour = item[15]

        avg_amount = 0
        if pay_amount == None:
            pay_amount = 0
        elif user_count > 0:
            avg_amount = pay_amount / user_count

        param = [date, city_id, city_name, area_id, area_name, local_id, local_name, cat1, cat1name, cat2, cat2name, 0,
                 '', user_count, avg_amount, total_top_hour, user_type]
        #print param;
        conn.insert_record(stat_table, param)

    conn.close()


def copy_from_mongo(date):
    mongo_conn = set_mongo_conn()
    stat_conn = set_stats_conn()
    log_table = "toptanxiao"
    stat_table = 'top_info_tanxiao_stats'

    query = {'date': date}
    items = find_items(mongo_conn, log_table, query)
    #print len(items)
    #return
    for item in items:
        logid = item['_id']
        #print logid
        cate1id = item['cate1id']
        cate1name = item['cate1name']
        cateid = item['cateid']
        catename = item['catename']
        city = item['cityid']
        #cityname = item[6]
        #date = item[7]
        infonum = item['infonum']
        oh = item['oh']
        sh = item['sh']
        tanxiao = item['tanxiao']
        usernum = item['usernum']

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]

        cate1name = cate1name.encode('utf-8')
        catename = catename.encode('utf-8')

        param = [date, logid, city_id, city_name, area_id, area_name, local_id, local_name, cate1id, cate1name, cateid,
                 catename, infonum, oh, sh, tanxiao, usernum]
        #print param
        stat_conn.insert_record(stat_table, param)

    stat_conn.close()
    #pass


def get_citys(city, conn):
    if city == 0:
        return None

    query = 'select fullpath from t_local where localid=%s' % city
    items = conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select localid, localname from t_local where localid in (%s) order by depth asc" % fullpath
    #print q
    return conn.get_results(query)


def get_cates(cate, conn):
    if cate == 0:
        return None

    query = 'select fullpath from t_category where cateid=%s' % cate
    items = conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select cateid, catename from t_category where cateid in (%s) order by depth asc" % fullpath
    #print q
    return conn.get_results(query)


def execute(date):
    get_top_info_price_data(date)
    #copy_from_mongo(date)
    try:
        pass
        #get_top_info_data(date)
    except Exception, e:
        print 'get_top_info_data:', e
    try:
        pass
        #get_top_info_cate_stat(date)
        #copy_from_mongo(date)
    except Exception, e:
        print 'get_top_info_cate_stat:', e


def main():
    #date = '2012-05-28'
    #execute(date)
    if len(sys.argv) == 1:
        start_date = get_yesterday()
        end_date = start_date
    elif len(sys.argv) == 2:
        start_date = format_date(sys.argv[1])
        end_date = start_date
    elif len(sys.argv) == 3:
        start_date = format_date(sys.argv[1])
        end_date = format_date(sys.argv[2])
    stat_date = start_date
    while stat_date >= start_date and stat_date <= end_date:
        print "The program of the date of %s is beginning at %s " % (stat_date, get_now())
        #
        execute(stat_date)
        print "The program of the date of %s is end at %s " % (stat_date, get_now())
        stat_date = var_date(stat_date)


if __name__ == '__main__':
    main()